/***************************************
*********** Q1 Find all problems of a single patient
*********** Patient: Historia cl�nica resumida - Paciente: ANON_SERV_RSC:0000000002
******************************************/
SELECT O.VALUE as Problema, paciente.VALUE as pac
FROM `Composition_content` as B, `RECORDCOMPONENT` as composition,
`Entry_items` as item, `RECORDCOMPONENT` as E, `Cluster_parts` as parts, `RECORDCOMPONENT` as G,
`CD` as name, `ED` as I, `Cluster_parts` as elements, `RECORDCOMPONENT` as K, `CD` as value, `ED` as M,
`CD` as N, `ED` as O,
(SELECT B.allCompositions_KKEY as ac, A.VALUE
FROM `EHRExtract_allCompositions` AS B, `ED` AS A, `RECORDCOMPONENT` AS C, `CD` AS D
WHERE B.allCompositions_KKEY = C.KKEY
AND C.NAME_KKEY = D.KKEY
AND D.ORIGINALTEXT_KKEY = A.KKEY and A.VALUE="Historia cl�nica resumida - Paciente: ANON_SERV_RSC:0000000002") AS paciente
WHERE 
B.content_KKEY=composition.KKEY and
composition.KKEY=item.Entry_KKEY and item.items_KKEY=E.KKEY and E.KKEY=parts.Cluster_KKEY and
parts.parts_KKEY=G.KKEY and G.NAME_KKEY=name.KKEY and name.ORIGINALTEXT_KKEY=I.KKEY and
I.VALUE = "Problema" and G.KKEY=elements.Cluster_KKEY and elements.parts_KKEY=K.KKEY and K.NAME_KKEY=value.KKEY and
value.ORIGINALTEXT_KKEY=M.KKEY and M.VALUE="Descripci�n" and K.VALUE_KKEY=N.KKEY and N.ORIGINALTEXT_KKEY=O.KKEY 
and paciente.ac=B.Composition_KKEY


/***************************************
*********** Q2 Find all problems of all patients
******************************************/
SELECT  paciente.VALUE as PACIENTE, O.VALUE as PROBLEMA
FROM `Composition_content` as B, 
`RECORDCOMPONENT` as composition, `Entry_items` as item, 
`RECORDCOMPONENT` as E, `Cluster_parts` as parts, 
`RECORDCOMPONENT` as G, `CD` as name, `ED` as I, `Cluster_parts` as elements, 
`RECORDCOMPONENT` as K, `CD` as value, `ED` as M, `CD` as N, `ED` as O, 
(SELECT B.allCompositions_KKEY as ac, A.VALUE FROM `EHRExtract_allCompositions` AS B, 
`ED` AS A, `RECORDCOMPONENT` AS C, `CD` AS D WHERE B.allCompositions_KKEY = C.KKEY 
AND C.NAME_KKEY = D.KKEY AND D.ORIGINALTEXT_KKEY = A.KKEY) AS paciente 
WHERE  B.content_KKEY=composition.KKEY and composition.KKEY=item.Entry_KKEY 
and item.items_KKEY=E.KKEY and E.KKEY=parts.Cluster_KKEY and parts.parts_KKEY=G.KKEY 
and G.NAME_KKEY=name.KKEY and name.ORIGINALTEXT_KKEY=I.KKEY and I.VALUE = "Problema" 
and G.KKEY=elements.Cluster_KKEY and elements.parts_KKEY=K.KKEY and K.NAME_KKEY=value.KKEY 
and value.ORIGINALTEXT_KKEY=M.KKEY and M.VALUE="Descripci�n" and K.VALUE_KKEY=N.KKEY 
and N.ORIGINALTEXT_KKEY=O.KKEY and paciente.ac=B.Composition_KKEY


/***************************************
*********** Q3 Find initial date, resolution date and severity 
*********** of a single problem of a single patient
*********** Problem: LUMBALGIA (SIN IRRADIACION)
*********** Patient: Historia cl�nica resumida - Paciente: ANON_SERV_RSC:0000004714
******************************************/
SELECT FIni.pac, FIni.Descr, FIni.SEVERIDAD, FIni.FechaIni, FR.VALUE as FRes
FROM `RECORDCOMPONENT` as G,
`CD` as name, `ED` as I, `Cluster_parts` as elements, `RECORDCOMPONENT` as K, `CD` as value, `ED` as M, `TS` as FR,

(SELECT SEVER.element, FAI.VALUE as FechaIni, SEVER.SEVERIDAD, SEVER.pac, SEVER.Descr
FROM `RECORDCOMPONENT` as G,
`CD` as name, `ED` as I, `Cluster_parts` as elements, `RECORDCOMPONENT` as K, `CD` as value, `ED` as M, `TS` as FAI,
(SELECT paciente_prob.element, SEVTAB.VALUE as SEVERIDAD, 
paciente_prob.pac, paciente_prob.Descr
FROM `RECORDCOMPONENT` as G,
`CD` as name, `ED` as I, `Cluster_parts` as elements, `RECORDCOMPONENT` as K, `CD` as value, `ED` as M, CO, CD as co_Key, ED as SEVTAB,
(SELECT B.Composition_KKEY, O.VALUE as Descr, paciente.VALUE as pac, G.KKEY as element
FROM `Composition_content` as B, `RECORDCOMPONENT` as composition,
`Entry_items` as item, `RECORDCOMPONENT` as E, `Cluster_parts` as parts, `RECORDCOMPONENT` as G,
`CD` as name, `ED` as I, `Cluster_parts` as elements, `RECORDCOMPONENT` as K, `CD` as value, `ED` as M,
`CD` as N, `ED` as O,
(SELECT B.allCompositions_KKEY as ac, A.VALUE
FROM `EHRExtract_allCompositions` AS B, `ED` AS A, `RECORDCOMPONENT` AS C, `CD` AS D
WHERE B.allCompositions_KKEY = C.KKEY
AND C.NAME_KKEY = D.KKEY
AND D.ORIGINALTEXT_KKEY = A.KKEY and A.VALUE="Historia cl�nica resumida - Paciente: ANON_SERV_RSC:0000004714") AS paciente
WHERE 
B.content_KKEY=composition.KKEY and
composition.KKEY=item.Entry_KKEY and item.items_KKEY=E.KKEY and E.KKEY=parts.Cluster_KKEY and
parts.parts_KKEY=G.KKEY and G.NAME_KKEY=name.KKEY and name.ORIGINALTEXT_KKEY=I.KKEY and
I.VALUE = "Problema" and G.KKEY=elements.Cluster_KKEY and elements.parts_KKEY=K.KKEY and K.NAME_KKEY=value.KKEY and
value.ORIGINALTEXT_KKEY=M.KKEY and M.VALUE="Descripci�n" and K.VALUE_KKEY=N.KKEY and N.ORIGINALTEXT_KKEY=O.KKEY 
and O.VALUE="LUMBALGIA (SIN IRRADIACION)" 
and paciente.ac=B.Composition_KKEY) as paciente_prob
WHERE 
paciente_prob.element = G.KKEY
and G.NAME_KKEY=name.KKEY and name.ORIGINALTEXT_KKEY=I.KKEY and
I.VALUE = "Problema" and G.KKEY=elements.Cluster_KKEY and elements.parts_KKEY=K.KKEY and K.NAME_KKEY=value.KKEY and
value.ORIGINALTEXT_KKEY=M.KKEY and  M.VALUE="Severidad" 
and K.VALUE_KKEY=CO.KKEY and CO.Code_KKEY = co_Key.KKEY and co_Key.OriginalText_kkey = SEVTAB.KKEY) as SEVER
WHERE 
SEVER.element = G.KKEY
and G.NAME_KKEY=name.KKEY and name.ORIGINALTEXT_KKEY=I.KKEY and
I.VALUE = "Problema" and G.KKEY=elements.Cluster_KKEY and elements.parts_KKEY=K.KKEY and K.NAME_KKEY=value.KKEY and
value.ORIGINALTEXT_KKEY=M.KKEY and M.VALUE="Fecha de aparici�n inicial" and K.VALUE_KKEY=FAI.KKEY )as FIni
WHERE 
FIni.element = G.KKEY
and G.NAME_KKEY=name.KKEY and name.ORIGINALTEXT_KKEY=I.KKEY and
I.VALUE = "Problema" and G.KKEY=elements.Cluster_KKEY and elements.parts_KKEY=K.KKEY and K.NAME_KKEY=value.KKEY and
value.ORIGINALTEXT_KKEY=M.KKEY and M.VALUE="Fecha de resoluci�n" and K.VALUE_KKEY=FR.KKEY


/***************************************
*********** Q4 Find initial date, resolution date and severity *********** of all problems of a single patient
*********** Patient: Historia cl�nica resumida - Paciente: ANON_SERV_RSC:0000004154
******************************************/
SELECT FIni.pac, FIni.Descr, FIni.SEVERIDAD, FIni.FechaIni, FR.VALUE as FRes
FROM `RECORDCOMPONENT` as G,
`CD` as name, `ED` as I, `Cluster_parts` as elements, `RECORDCOMPONENT` as K, `CD` as value, `ED` as M, `TS` as FR,
(SELECT SEVER.element, FAI.VALUE as FechaIni, SEVER.SEVERIDAD, SEVER.pac, SEVER.Descr
FROM `RECORDCOMPONENT` as G,
`CD` as name, `ED` as I, `Cluster_parts` as elements, `RECORDCOMPONENT` as K, `CD` as value, `ED` as M, `TS` as FAI,
(SELECT paciente_prob.element, SEVTAB.VALUE as SEVERIDAD, 
paciente_prob.pac, paciente_prob.Descr
FROM `RECORDCOMPONENT` as G,
`CD` as name, `ED` as I, `Cluster_parts` as elements, `RECORDCOMPONENT` as K, `CD` as value, `ED` as M, CO, CD as co_Key, ED as SEVTAB,
(SELECT B.Composition_KKEY, O.VALUE as Descr, paciente.VALUE as pac, G.KKEY as element
FROM `Composition_content` as B, `RECORDCOMPONENT` as composition,
`Entry_items` as item, `RECORDCOMPONENT` as E, `Cluster_parts` as parts, `RECORDCOMPONENT` as G,
`CD` as name, `ED` as I, `Cluster_parts` as elements, `RECORDCOMPONENT` as K, `CD` as value, `ED` as M,
`CD` as N, `ED` as O,
(SELECT B.allCompositions_KKEY as ac, A.VALUE
FROM `EHRExtract_allCompositions` AS B, `ED` AS A, `RECORDCOMPONENT` AS C, `CD` AS D
WHERE B.allCompositions_KKEY = C.KKEY
AND C.NAME_KKEY = D.KKEY
AND D.ORIGINALTEXT_KKEY = A.KKEY and A.VALUE="Historia cl�nica resumida - Paciente: ANON_SERV_RSC:0000004154") AS paciente
WHERE 
B.content_KKEY=composition.KKEY and
composition.KKEY=item.Entry_KKEY and item.items_KKEY=E.KKEY and E.KKEY=parts.Cluster_KKEY and
parts.parts_KKEY=G.KKEY and G.NAME_KKEY=name.KKEY and name.ORIGINALTEXT_KKEY=I.KKEY and
I.VALUE = "Problema" and G.KKEY=elements.Cluster_KKEY and elements.parts_KKEY=K.KKEY and K.NAME_KKEY=value.KKEY and
value.ORIGINALTEXT_KKEY=M.KKEY and M.VALUE="Descripci�n" and K.VALUE_KKEY=N.KKEY and N.ORIGINALTEXT_KKEY=O.KKEY 
and paciente.ac=B.Composition_KKEY) as paciente_prob
WHERE 
paciente_prob.element = G.KKEY
and G.NAME_KKEY=name.KKEY and name.ORIGINALTEXT_KKEY=I.KKEY and
I.VALUE = "Problema" and G.KKEY=elements.Cluster_KKEY and elements.parts_KKEY=K.KKEY and K.NAME_KKEY=value.KKEY and
value.ORIGINALTEXT_KKEY=M.KKEY and M.VALUE="Severidad" 
and K.VALUE_KKEY=CO.KKEY and CO.Code_KKEY = co_Key.KKEY and co_Key.OriginalText_kkey = SEVTAB.KKEY) as SEVER
WHERE 
SEVER.element = G.KKEY
and G.NAME_KKEY=name.KKEY and name.ORIGINALTEXT_KKEY=I.KKEY and
I.VALUE = "Problema" and G.KKEY=elements.Cluster_KKEY and elements.parts_KKEY=K.KKEY and K.NAME_KKEY=value.KKEY and
value.ORIGINALTEXT_KKEY=M.KKEY and M.VALUE="Fecha de aparici�n inicial" and K.VALUE_KKEY=FAI.KKEY )as FIni
WHERE 
FIni.element = G.KKEY
and G.NAME_KKEY=name.KKEY and name.ORIGINALTEXT_KKEY=I.KKEY and
I.VALUE = "Problema" and G.KKEY=elements.Cluster_KKEY and elements.parts_KKEY=K.KKEY and K.NAME_KKEY=value.KKEY and
value.ORIGINALTEXT_KKEY=M.KKEY and M.VALUE="Fecha de resoluci�n" and K.VALUE_KKEY=FR.KKEY


/***************************************
*********** Q5 Find initial date, resolution date and severity 
*********** of all problems of all patients
******************************************/
SELECT FIni.pac, FIni.Descr, FIni.SEVERIDAD, FIni.FechaIni, FR.VALUE as FRes
FROM `RECORDCOMPONENT` as G,
`CD` as name, `ED` as I, `Cluster_parts` as elements, `RECORDCOMPONENT` as K, `CD` as value, `ED` as M, `TS` as FR,
(SELECT SEVER.element, FAI.VALUE as FechaIni, SEVER.SEVERIDAD, SEVER.pac, SEVER.Descr
FROM `RECORDCOMPONENT` as G,
`CD` as name, `ED` as I, `Cluster_parts` as elements, `RECORDCOMPONENT` as K, `CD` as value, `ED` as M, `TS` as FAI,
(SELECT paciente_prob.element, SEVTAB.VALUE as SEVERIDAD, 
paciente_prob.pac, paciente_prob.Descr
FROM `RECORDCOMPONENT` as G,
`CD` as name, `ED` as I, `Cluster_parts` as elements, `RECORDCOMPONENT` as K, `CD` as value, `ED` as M, CO, CD as co_Key, ED as SEVTAB,
(SELECT B.Composition_KKEY, O.VALUE as Descr, paciente.VALUE as pac, G.KKEY as element
FROM `Composition_content` as B, `RECORDCOMPONENT` as composition,
`Entry_items` as item, `RECORDCOMPONENT` as E, `Cluster_parts` as parts, `RECORDCOMPONENT` as G,
`CD` as name, `ED` as I, `Cluster_parts` as elements, `RECORDCOMPONENT` as K, `CD` as value, `ED` as M,
`CD` as N, `ED` as O,
(SELECT B.allCompositions_KKEY as ac, A.VALUE
FROM `EHRExtract_allCompositions` AS B, `ED` AS A, `RECORDCOMPONENT` AS C, `CD` AS D
WHERE B.allCompositions_KKEY = C.KKEY
AND C.NAME_KKEY = D.KKEY
AND D.ORIGINALTEXT_KKEY = A.KKEY) AS paciente
WHERE 
B.content_KKEY=composition.KKEY and
composition.KKEY=item.Entry_KKEY and item.items_KKEY=E.KKEY and E.KKEY=parts.Cluster_KKEY and
parts.parts_KKEY=G.KKEY and G.NAME_KKEY=name.KKEY and name.ORIGINALTEXT_KKEY=I.KKEY and
I.VALUE = "Problema" and G.KKEY=elements.Cluster_KKEY and elements.parts_KKEY=K.KKEY and K.NAME_KKEY=value.KKEY and
value.ORIGINALTEXT_KKEY=M.KKEY and M.VALUE="Descripci�n" and K.VALUE_KKEY=N.KKEY and N.ORIGINALTEXT_KKEY=O.KKEY  
and paciente.ac=B.Composition_KKEY) as paciente_prob
WHERE 
paciente_prob.element = G.KKEY
and G.NAME_KKEY=name.KKEY and name.ORIGINALTEXT_KKEY=I.KKEY and
I.VALUE = "Problema" and G.KKEY=elements.Cluster_KKEY and elements.parts_KKEY=K.KKEY and K.NAME_KKEY=value.KKEY and
value.ORIGINALTEXT_KKEY=M.KKEY and M.VALUE="Severidad" 
and K.VALUE_KKEY=CO.KKEY and CO.Code_KKEY = co_Key.KKEY and co_Key.OriginalText_kkey = SEVTAB.KKEY) as SEVER
WHERE 
SEVER.element = G.KKEY
and G.NAME_KKEY=name.KKEY and name.ORIGINALTEXT_KKEY=I.KKEY and
I.VALUE = "Problema" and G.KKEY=elements.Cluster_KKEY and elements.parts_KKEY=K.KKEY and K.NAME_KKEY=value.KKEY and
value.ORIGINALTEXT_KKEY=M.KKEY and M.VALUE="Fecha de aparici�n inicial" and K.VALUE_KKEY=FAI.KKEY )as FIni
WHERE 
FIni.element = G.KKEY
and G.NAME_KKEY=name.KKEY and name.ORIGINALTEXT_KKEY=I.KKEY and
I.VALUE = "Problema" and G.KKEY=elements.Cluster_KKEY and elements.parts_KKEY=K.KKEY and K.NAME_KKEY=value.KKEY and
value.ORIGINALTEXT_KKEY=M.KKEY and M.VALUE="Fecha de resoluci�n" and K.VALUE_KKEY=FR.KKEY


/***************************************
*********** Q6 Find all patients with:
*********** Problem: FARINGITIS
*********** Severity: Muy severo
*********** Initial date: 20071016000000.0
*********** Resolution date: 20080506000000.0
******************************************/
SELECT A.VALUE, FecRes.Descr, FecRes.SEVERIDAD, FecRes.FechaIni, FecRes.FRes
FROM `EHRExtract_allCompositions` AS B, `ED` AS A, `RECORDCOMPONENT` AS C, `CD` AS D,
	(SELECT FIni.Descr, FIni.SEVERIDAD, FIni.FechaIni, FR.VALUE as FRes, G.KKEY as elementm, FIni.compo
	FROM `RECORDCOMPONENT` as G,
	`CD` as name, `ED` as I, `Cluster_parts` as elements, `RECORDCOMPONENT` as K, `CD` as value, `ED` as M, `TS` as FR,

		(SELECT SEVER.element, FAI.VALUE as FechaIni, SEVER.SEVERIDAD, SEVER.Descr, SEVER.compo
		FROM `RECORDCOMPONENT` as G,
		`CD` as name, `ED` as I, `Cluster_parts` as elements, `RECORDCOMPONENT` as K, `CD` as value, `ED` as M, `TS` as FAI,

			(SELECT prob.element, SEVTAB.VALUE as SEVERIDAD, prob.Descr, prob.compo
			FROM `RECORDCOMPONENT` as G,
			`CD` as name, `ED` as I, `Cluster_parts` as elements, `RECORDCOMPONENT` as K, `CD` as value, `ED` as M, CO, CD as co_Key, ED as SEVTAB,

				(SELECT O.VALUE as Descr, G.KKEY as element, B.Composition_KKEY as compo
				FROM `Composition_content` as B, `RECORDCOMPONENT` as composition,
				`Entry_items` as item, `RECORDCOMPONENT` as E, `Cluster_parts` as parts, `RECORDCOMPONENT` as G,
				`CD` as name, `ED` as I, `Cluster_parts` as elements, `RECORDCOMPONENT` as K, `CD` as value, `ED` as M,
				`CD` as N, `ED` as O
				WHERE 
				B.content_KKEY=composition.KKEY and
				composition.KKEY=item.Entry_KKEY and item.items_KKEY=E.KKEY and E.KKEY=parts.Cluster_KKEY and
				parts.parts_KKEY=G.KKEY and G.NAME_KKEY=name.KKEY and name.ORIGINALTEXT_KKEY=I.KKEY and
				I.VALUE = "Problema" and G.KKEY=elements.Cluster_KKEY and elements.parts_KKEY=K.KKEY and K.NAME_KKEY=value.KKEY and
				value.ORIGINALTEXT_KKEY=M.KKEY and M.VALUE="Descripci�n" and K.VALUE_KKEY=N.KKEY and N.ORIGINALTEXT_KKEY=O.KKEY  
				and O.VALUE="FARINGITIS") as prob
			WHERE 
			prob.element = G.KKEY
			and G.NAME_KKEY=name.KKEY and name.ORIGINALTEXT_KKEY=I.KKEY and
			I.VALUE = "Problema" and G.KKEY=elements.Cluster_KKEY and elements.parts_KKEY=K.KKEY and K.NAME_KKEY=value.KKEY and
			value.ORIGINALTEXT_KKEY=M.KKEY and M.VALUE="Severidad" 
			and K.VALUE_KKEY=CO.KKEY 
			and CO.Code_KKEY = co_Key.KKEY and co_Key.OriginalText_kkey = SEVTAB.KKEY 
			and SEVTAB.VALUE = "Muy severo") as SEVER
		WHERE 
		SEVER.element = G.KKEY
		and G.NAME_KKEY=name.KKEY and name.ORIGINALTEXT_KKEY=I.KKEY and
		I.VALUE = "Problema" and G.KKEY=elements.Cluster_KKEY and elements.parts_KKEY=K.KKEY and K.NAME_KKEY=value.KKEY and
		value.ORIGINALTEXT_KKEY=M.KKEY and M.VALUE="Fecha de aparici�n inicial" and K.VALUE_KKEY=FAI.KKEY 
		and FAI.VALUE>="20071016000000.0")as FIni
	WHERE 
	FIni.element = G.KKEY
	and G.NAME_KKEY=name.KKEY and name.ORIGINALTEXT_KKEY=I.KKEY and
	I.VALUE = "Problema" and G.KKEY=elements.Cluster_KKEY and elements.parts_KKEY=K.KKEY and K.NAME_KKEY=value.KKEY and
	value.ORIGINALTEXT_KKEY=M.KKEY and M.VALUE="Fecha de resoluci�n" and K.VALUE_KKEY=FR.KKEY and FR.VALUE >= "20080506000000.0") as FecRes
WHERE B.allCompositions_KKEY = C.KKEY
AND C.NAME_KKEY = D.KKEY
AND D.ORIGINALTEXT_KKEY = A.KKEY and B.allCompositions_KKEY = FecRes.compo